package com.aierkeji.call.b2c.dao.jpa.custom.ipml;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.aierkeji.call.b2c.dao.jpa.custom.AkjCallClueStatisticsDaoCustom;
import com.aierkeji.call.b2c.entity.AkjCallClueStatistics;
import com.aierkeji.call.b2c.entity.AkjCallClueStatisticsVo;
import com.aierkeji.call.b2c.util.DateTimeUtils;
import com.aierkeji.call.b2c.util.StringUtils;

public class AkjCallClueStatisticsDaoCustomImpl implements AkjCallClueStatisticsDaoCustom{
	private final Logger log = LogManager.getLogger(getClass());

	@Autowired
	JdbcTemplate jdbcTemplate;

	@Override
	public AkjCallClueStatistics getStatisticsByProcedure(Long userId,Long groupId, Date createdAt) {
		final AkjCallClueStatistics statistics = new AkjCallClueStatistics();
		AkjCallClueStatistics result = (AkjCallClueStatistics) jdbcTemplate.execute(
		            new CallableStatementCreator() {
		                public CallableStatement createCallableStatement(java.sql.Connection con) throws SQLException {
		                    String storedProc = "{call update_call_statisics(?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
		                    CallableStatement cs = con.prepareCall(storedProc);
		                    cs.setLong(1,userId);// 设置输入参数的值
		                    cs.setLong(2, groupId);// 设置输入参数的值
		                    cs.setString(3, DateTimeUtils.converDateToString(createdAt, DateTimeUtils.DATE_PATTERN_TIMESTAMP));
		                    cs.registerOutParameter(4, Types.BIGINT);// 注册输出参数的类型
		                    cs.registerOutParameter(5, Types.BIGINT);
		                    cs.registerOutParameter(6, Types.BIGINT);
		                    cs.registerOutParameter(7, Types.BIGINT);
		                    cs.registerOutParameter(8, Types.BIGINT);
		                    cs.registerOutParameter(9, Types.BIGINT);
		                    cs.registerOutParameter(10, Types.BIGINT);
		                    cs.registerOutParameter(11, Types.BIGINT);
		                    cs.registerOutParameter(12, Types.BIGINT);
		                    cs.registerOutParameter(13, Types.BIGINT);
		                    cs.registerOutParameter(14, Types.BIGINT);
		                    return cs;
		                }
		            }, new CallableStatementCallback() {
		                public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {
		                    cs.execute();
		                    Long csLong = cs.getLong(4);
		                    if (csLong<=0) {
		                        return null;
		                    }else{
		                    	statistics.setClueTotal(cs.getLong(4));
		                    	statistics.setCallTime(cs.getLong(5));
		                    	statistics.setNetCalledTotal(cs.getLong(6));
		                    	statistics.setUnCalledTotal(cs.getLong(7));
		                    	statistics.setNetConnectTotal(cs.getLong(8));
		                    	statistics.setUnConnectTotal(cs.getLong(9));
		                    	statistics.setUnConfirmTotal(cs.getLong(10));
		                    	statistics.setConfirmTotal(cs.getLong(11));
		                    	statistics.setWaitTotal(cs.getLong(12));
		                    	statistics.setCalledTotal(cs.getLong(13));
		                    	statistics.setConnectTotal(cs.getLong(14));
		                        return statistics;
		                    }
		                }
		            });
		    return statistics;
		}
	
	

	@Override
	public AkjCallClueStatisticsVo getUserStatistics(Long id, String initDate, String finishDate) {
		
		String sql = "SELECT" + 
				"	SUM(t.call_time) as call_time, " + 
				"	SUM(t.called_total) as called_total, " + 
				"	SUM(t.clue_total) as clue_total, " + 
				"	SUM(t.confirm_total) as confirm_total, " + 
				"	SUM(t.connect_total) as connect_total, " + 
				"	SUM(t.un_called_total) as un_called_total, " + 
				"	SUM(t.un_confirm_total) as un_confirm_total, " + 
				"	SUM(t.un_connect_total) as un_connect_total, " + 
				"	SUM(t.wait_total) as wait_total, " + 
				"	SUM(t.net_called_total) as net_called_total, "+
				"	SUM(t.un_called_total) as un_called_total, "+
				"	SUM(t.net_called_total) as net_called_total, "+
				"	SUM(t.net_connect_total) as net_connect_total "+
				"FROM" + 
				"	akj_call_statistics t " + 
				"WHERE" + 
				"	t.call_user_id = ?" + 
				"	AND t.created_at BETWEEN ? AND ?;";
		
		List<Object> queryList = new ArrayList<Object>();
		queryList.add(id);
		queryList.add(initDate);
		queryList.add(finishDate);
		AkjCallClueStatisticsVo akjCallClueStatisticsVo = new AkjCallClueStatisticsVo();
		 akjCallClueStatisticsVo = jdbcTemplate.queryForObject(sql, new RowMapper<AkjCallClueStatisticsVo>() {  
		    public AkjCallClueStatisticsVo mapRow(ResultSet rs, int rowNum) throws SQLException {  
		    	AkjCallClueStatisticsVo vo = new AkjCallClueStatisticsVo();  
		    	if(rs.getLong("clue_total") == 0) {
		    		return null;
		    	}
		    	vo.setCalledTotal(rs.getLong("called_total"));
		    	vo.setCallTime(rs.getLong("call_time"));
		    	vo.setClueTotal(rs.getLong("clue_total"));
		    	vo.setConfirmTotal(rs.getLong("confirm_total"));
		    	vo.setConnectTotal(rs.getLong("connect_total"));
		    	vo.setUnCalledTotal(rs.getLong("un_called_total"));
		    	vo.setUnConfirmTotal(rs.getLong("un_confirm_total"));
		    	vo.setUnConnectTotal(rs.getLong("un_connect_total"));
		    	vo.setWaitTotal(rs.getLong("wait_total"));
		    	vo.setElseCallTota(rs.getLong("called_total")-rs.getLong("net_called_total"));
		    	
		    	java.text.DecimalFormat myformat=new java.text.DecimalFormat("0.00");
		    	if(vo.getCalledTotal()>0) {
		    		
		    		
		    		if(vo.getNetCalledTotal()!=null&&vo.getNetCalledTotal()>0) {	
				    	BigDecimal bgBigDecimal = new BigDecimal(((float)rs.getLong("confirm_total")/rs.getLong("net_called_total")*100));
				    	String confirm_total = myformat.format(bgBigDecimal.doubleValue());   
				    	vo.setConfirmPercent(Float.parseFloat(confirm_total));
		    		}
			    	
			    	if(vo.getCalledTotal()!=null&&vo.getCalledTotal()>0) {
				    	BigDecimal ConnectPercent = new BigDecimal(((float)rs.getLong("net_connect_total")/rs.getLong("called_total")*100));
				    	String ConnectPercentStr = myformat.format(ConnectPercent.doubleValue());   
				    	vo.setConnectPercent(Float.parseFloat(ConnectPercentStr));
			    	}
			    
			    	if(vo.getClueTotal()!=null&&vo.getClueTotal()>0) {
				    	BigDecimal un_called_total = new BigDecimal(((float)rs.getLong("net_called_total")/rs.getLong("clue_total")*100));
				    	String un_called_totalStr = myformat.format(un_called_total.doubleValue());   
				    	vo.setFinishPercent(Float.parseFloat(un_called_totalStr));
			    	}
		    	
		    	}
		    	vo.setNetCalledTotal(rs.getLong("net_called_total"));
		    return vo;  
		    }  
		  },queryList.toArray());
		
		 akjCallClueStatisticsVo=akjCallClueStatisticsVo==null?new AkjCallClueStatisticsVo():akjCallClueStatisticsVo;
		return akjCallClueStatisticsVo;
	}



	@Override
	public void callStatisticsProcedure(String leaderId, String userId, String initDate, String finishDate,String speciallyTime) {
		speciallyTime = StringUtils.isEmpty(speciallyTime) ? "" : speciallyTime;
		if(StringUtils.isEmpty(leaderId)) {
			leaderId = "-99";
		}
		if(StringUtils.isEmpty(userId)){
			userId = "-99";
		}
		log.info("leaderId:{},userId:{}", leaderId, userId);
		
		jdbcTemplate.execute("call update_call_statisics("+leaderId+","+userId+",'"+initDate+"','"+finishDate+"','"+speciallyTime+"')");
		
	}



	@Override
	public AkjCallClueStatisticsVo getStatisticsByAssigneUser(String assigneUserId, Long leaderId, String initDate,
			String finishDate) {
		
		List<Object> queryList = new ArrayList<Object>();
		
		String sql = "SELECT" + 
				"	SUM(t.call_time) as call_time, " + 
				"	SUM(t.called_total) as called_total, " + 
				"	SUM(t.clue_total) as clue_total, " + 
				"	SUM(t.confirm_total) as confirm_total, " + 
				"	SUM(t.connect_total) as connect_total, " + 
				"	SUM(t.un_called_total) as un_called_total, " + 
				"	SUM(t.un_confirm_total) as un_confirm_total, " + 
				"	SUM(t.un_connect_total) as un_connect_total, " + 
				"	SUM(t.wait_total) as wait_total, " + 
				"	SUM(t.net_called_total) as net_called_total, "+
				"	SUM(t.un_called_total) as un_called_total, "+
				"	SUM(t.net_called_total) as net_called_total, "+
				"	SUM(t.net_connect_total) as net_connect_total "+
				"FROM" + 
				"	akj_call_statistics t";
		
		if(StringUtils.isEmpty(assigneUserId)) {
			sql += " join akj_call_user t1 "+
					" on t.call_user_id = t1.id "+
					" where t1.leader_id = ? ";
			queryList.add(leaderId);
		}else {
			sql += " where 1=1 and t.call_user_id = ? " ;
			queryList.add(assigneUserId);
		}
		
		sql	+= " AND t.created_at BETWEEN ? AND ? ; ";
		
		queryList.add(initDate);
		queryList.add(finishDate);
		
		
		AkjCallClueStatisticsVo akjCallClueStatisticsVo = new AkjCallClueStatisticsVo();
		 akjCallClueStatisticsVo = jdbcTemplate.queryForObject(sql, new RowMapper<AkjCallClueStatisticsVo>() {  
		    public AkjCallClueStatisticsVo mapRow(ResultSet rs, int rowNum) throws SQLException {  
		    	AkjCallClueStatisticsVo vo = new AkjCallClueStatisticsVo();  
		    	if(rs.getLong("clue_total") == 0) {
		    		return null;
		    	}
		    	vo.setCalledTotal(rs.getLong("called_total"));
		    	vo.setCallTime(rs.getLong("call_time"));
		    	vo.setClueTotal(rs.getLong("clue_total"));
		    	vo.setConfirmTotal(rs.getLong("confirm_total"));
		    	vo.setConnectTotal(rs.getLong("connect_total"));
		    	vo.setUnCalledTotal(rs.getLong("un_called_total"));
		    	vo.setUnConfirmTotal(rs.getLong("un_confirm_total"));
		    	vo.setUnConnectTotal(rs.getLong("un_connect_total"));
		    	vo.setWaitTotal(rs.getLong("wait_total"));
		    	vo.setElseCallTota(rs.getLong("called_total")-rs.getLong("net_called_total"));
		    	
		    	java.text.DecimalFormat myformat=new java.text.DecimalFormat("0.00");
		    	if(vo.getConnectTotal()!=null&&vo.getConnectTotal()>0) {
			    	BigDecimal avgTimeDecimal = new BigDecimal(((float)rs.getLong("call_time")/rs.getLong("connect_total")));
			    	String avgTime = myformat.format(avgTimeDecimal.doubleValue());   
			    	vo.setAvgTime(Float.parseFloat(avgTime));
		    	}
		    	if(vo.getCalledTotal()!=null&&vo.getCalledTotal()>0) {
			    	BigDecimal bgBigDecimal = new BigDecimal(((float)rs.getLong("confirm_total")/rs.getLong("net_called_total")*100));
			    	String confirm_total = myformat.format(bgBigDecimal.doubleValue());   
			    	vo.setConfirmPercent(Float.parseFloat(confirm_total));
		    	}
		    	if(vo.getCalledTotal()!=null&&vo.getCalledTotal()>0) {
			    	BigDecimal ConnectPercent = new BigDecimal(((float)rs.getLong("net_connect_total")/rs.getLong("called_total")*100));
			    	String ConnectPercentStr = myformat.format(ConnectPercent.doubleValue());   
			    	vo.setConnectPercent(Float.parseFloat(ConnectPercentStr));
		    	}
		    	if(vo.getClueTotal()!=null&&vo.getClueTotal()>0) {
			    	BigDecimal un_called_total = new BigDecimal(((float)rs.getLong("net_called_total")/rs.getLong("clue_total")*100));
			    	String un_called_totalStr = myformat.format(un_called_total.doubleValue());   
			    	vo.setFinishPercent(Float.parseFloat(un_called_totalStr));
		    	}
		    	
		    	vo.setNetCalledTotal(rs.getLong("net_called_total"));
		    return vo;  
		    }  
		  },queryList.toArray());
		
		 akjCallClueStatisticsVo=akjCallClueStatisticsVo==null?new AkjCallClueStatisticsVo():akjCallClueStatisticsVo;
		return akjCallClueStatisticsVo;
	}

}
